package cn.tiancaifan.dao.impl;

import cn.tiancaifan.dao.UserDaoI;
import cn.tiancaifan.entity.User;
import cn.tiancaifan.utils.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class UserDaoImpl implements UserDaoI {

    private Connection connection = DBUtil.getDbUtil().getConnection();

    @Override
    public User findUserByAccount(String account) {
        String sql = "select * from user where account = ?";
        User user = null;
        try {
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, account);
            ResultSet rs = preparedStatement.executeQuery();
            while (rs.next()) {
                user = new User();
                user.setId(rs.getLong("id"));
                user.setAccount(rs.getString("account"));
                user.setPassword(rs.getString("password"));
                user.setNikename(rs.getString("nikename"));
                user.setDistrictId(rs.getLong("district_id"));
                user.setAddress(rs.getString("address"));
                user.setRole(rs.getLong("role"));
                user.setAvatar(rs.getString("avatar"));
                user.setTel(rs.getString("tel"));
                user.setCreateId(rs.getLong("create_id"));
                user.setUpdateId(rs.getLong("update_id"));
                user.setCreateTime(rs.getString("create_time"));
                user.setUpdateTime(rs.getString("update_time"));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return user;
    }

    @Override
    public int insertUser(User user) {
        String sql = "insert into user (account,password,nikename,role,avatar,create_id,update_id,create_time,update_time)" +
                "values(?,?,?,?,?,?,?,?,?)";
        int i = 0;
        try {
            PreparedStatement ps = connection.prepareStatement(sql);
            ps.setString(1, user.getAccount());
            ps.setString(2, user.getPassword());
            ps.setString(3, user.getNikename());
            ps.setLong(4, user.getRole());
            ps.setString(5, user.getAvatar());
            ps.setLong(6, user.getCreateId());
            ps.setLong(7, user.getUpdateId());
            ps.setString(8, user.getCreateTime());
            ps.setString(9, user.getUpdateTime());
            i = ps.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return i;
    }

    @Override
    public List<User> findAllUser(long id) {
        String sql = "select id,account,nikename,district_id,address,role,avatar,tel,create_id,update_id,create_time,update_time from user where id != ? and role != 3";
        PreparedStatement preparedStatement = null;
        List<User> users = new ArrayList();
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setLong(1,id);
            ResultSet rs = preparedStatement.executeQuery();
            while (rs.next()) {
                User user = new User();
                user.setId(rs.getLong("id"));
                user.setAccount(rs.getString("account"));
                user.setNikename(rs.getString("nikename"));
                user.setDistrictId(rs.getLong("district_id"));
                user.setAddress(rs.getString("address"));
                user.setRole(rs.getLong("role"));
                user.setAvatar(rs.getString("avatar"));
                user.setTel(rs.getString("tel"));
                user.setCreateId(rs.getLong("create_id"));
                user.setUpdateId(rs.getLong("update_id"));
                user.setCreateTime(rs.getString("create_time"));
                user.setUpdateTime(rs.getString("update_time"));
                users.add(user);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return users;
    }

    @Override
    public List<User> findUserBycondition(User parameters, long id) {
        String sql = "select id,account,nikename,district_id,address,role,avatar,tel,create_id,update_id,create_time,update_time from user where id != ? and role != 3";
        sql = splicingSql(sql,parameters);
        System.out.println(sql);
        PreparedStatement preparedStatement = null;
        List<User> users = new ArrayList();
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setLong(1,id);
            ResultSet rs = preparedStatement.executeQuery();
            while (rs.next()) {
                User user = new User();
                user.setId(rs.getLong("id"));
                user.setAccount(rs.getString("account"));
                user.setNikename(rs.getString("nikename"));
                user.setDistrictId(rs.getLong("district_id"));
                user.setAddress(rs.getString("address"));
                user.setRole(rs.getLong("role"));
                user.setAvatar(rs.getString("avatar"));
                user.setTel(rs.getString("tel"));
                user.setCreateId(rs.getLong("create_id"));
                user.setUpdateId(rs.getLong("update_id"));
                user.setCreateTime(rs.getString("create_time"));
                user.setUpdateTime(rs.getString("update_time"));
                users.add(user);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return users;
    }

    @Override
    public int deleteUserByid(String[] ids) {
        String sql = "delete from user where";
        int index= 0;
        sql = splicingSql(sql, ids);
        try {
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            for (int i = 1; i <= ids.length; i++) {
                preparedStatement.setString(i,ids[i-1]);
            }
            index = preparedStatement.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return index;
    }

    @Override
    public int updateUserById(User user) {
        String sql = "update user set nikename=?,district_id=?,address = ?,role = ?,avatar = ?,tel = ?, update_id = ?,update_time = ? where id = ?";
        int index = 0;
        try {
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, user.getNikename());
            preparedStatement.setLong(2, user.getDistrictId());
            preparedStatement.setString(3, user.getAddress());
            preparedStatement.setLong(4,user.getRole());
            preparedStatement.setString(5, user.getAvatar());
            preparedStatement.setString(6,user.getTel());
            preparedStatement.setLong(7,user.getUpdateId());
            preparedStatement.setString(8,user.getUpdateTime());
            preparedStatement.setLong(9,user.getId());
            index = preparedStatement.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return index;
    }

    private String splicingSql(String sql,User parameters){
        String account = parameters.getAccount();
        if (account!=null){
            sql+=" and account = '"+ account+"'";
        }
        String nikename = parameters.getNikename();
        if (nikename != null){
            sql+=" and nikename ='" + nikename+"'";
        }
        long role = parameters.getRole();
        if (role != 0 ){
            sql+=" and role =" + role;
        }
        long districtId = parameters.getDistrictId();
        if (districtId != 0 ){
            sql+=" and district_id =" + districtId;
        }
        return sql;
    }

    private String splicingSql(String sql,String[] ids){
        for (int i = 0;i<ids.length-1; i++){
            sql+=" id = ? or ";
        }
        sql+= " id = ?";
        return sql;
    }
}